﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections;
using System.Data.SqlClient;
using System.Configuration;
using Trying1;

namespace Trying1.Classes
{
    class ReportDBManager
    {
        public static ArrayList GetAllRoomsandStatus()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Room";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Room r = new Room();

                    r.roomNum = (int)dr["roomNum"];
                    r.roomStatus = (string)dr["roomStatus"];
                    r.roomRate = (decimal)dr["roomRate"];
                    r.roomType = (string)dr["roomType"];
                    result.Add(r);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }
        public static Room GetRoomAndStatus(int roomNum)
        {
            Room r = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Room WHERE roomNum=@roomNum";
                comm.Parameters.AddWithValue("@roomNum", roomNum);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    r = new Room();

                    r.roomNum = (int)dr["roomNum"];
                    r.roomStatus = (string)dr["roomStatus"];
                    r.roomRate = (decimal)dr["roomRate"];
                    r.roomType = (string)dr["roomType"];
                    
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return r;
        }

        public static Guest GetGuestByRoomNum(int roomNum)
        {
            Guest m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open(); SqlCommand comm = new SqlCommand();
                comm.Connection = conn; comm.CommandText = "SELECT * FROM Guest WHERE roomNum=@roomNum";
                comm.Parameters.AddWithValue("@roomNum", roomNum);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new Guest();
                    m.firstName = (string)dr["firstName"];
                    m.lastName = (string)dr["lastName"];
                    m.noOfGuest = (int)dr["noOfGuest"];
                    m.noOfChildren = (int)dr["noOfChildren"];
                    m.phoneNum = (Int64)dr["phoneNum"];
                    m.emailAddress = (string)dr["emailAddress"];
                    m.streetAddress = (string)dr["streetAddress"];
                    m.houseNo = (string)dr["houseNo"];
                    m.postalCode = (string)dr["postalCode"];
                    m.country = (string)dr["country"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }

        public static ArrayList GetAllGuestsInAllRoomsOnASpecificDate(DateTime date)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select g.guestNo,firstName,lastName,noOfGuest,noOfChildren,phoneNum,emailAddress,streetAddress,houseNo,postalCode,country from Booking b, Guest g where b.guestNo=g.guestNo AND @date BETWEEN checkInDate AND checkOutDate OR @date IN(checkInDate, checkOutDate)";
                comm.Parameters.AddWithValue("@date", date);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                   Guest m = new Guest();

                   m.firstName = (string)dr["firstName"];
                   m.lastName = (string)dr["lastName"];
                   m.noOfGuest = (int)dr["noOfGuest"];
                   m.noOfChildren = (int)dr["noOfChildren"];
                   m.phoneNum = (Int64)dr["phoneNum"];
                   m.emailAddress = (string)dr["emailAddress"];
                   m.streetAddress = (string)dr["streetAddress"];
                   m.houseNo = (string)dr["houseNo"];
                   m.postalCode = (string)dr["postalCode"];
                   m.country = (string)dr["country"];
                    
                   result.Add(m);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static ArrayList RoomOccupancyReportForARangeofDates(DateTime date1, DateTime date2)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Booking WHERE checkInDate >= @checkInDate and checkOutDate <= @checkOutDate";
                comm.Parameters.AddWithValue("@checkInDate", date1);
                comm.Parameters.AddWithValue("@checkOutDate", date2);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Booking r = new Booking();
                    r.bookingId = (int)dr["bookingId"];
                    r.roomNum = (int)dr["roomNum"];
                    r.guestNo = (int)dr["guestNo"];
                    r.checkInDate = (DateTime)dr["checkInDate"];
                    r.checkOutDate = (DateTime)dr["checkOutDate"];
                    result.Add(r);
                    
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static int RoomOccupancyReportForARangeofDatesCount(DateTime date1, DateTime date2)
        {
            int count = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Booking WHERE checkInDate >= @checkInDate and checkOutDate <= @checkOutDate";
                comm.Parameters.AddWithValue("@checkInDate", date1);
                comm.Parameters.AddWithValue("@checkOutDate", date2);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    count++;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return count;
        }
        public static ArrayList HoseKeepingReports(DateTime date1, DateTime date2)
        {

            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from HouseKeeping WHERE dateOfAssignment Between @Date1 and @Date2";
                comm.Parameters.AddWithValue("@Date1", date1);
                comm.Parameters.AddWithValue("@Date2", date2);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    HouseKeeping r = new HouseKeeping();

                    r.dateOfAssignment = (DateTime)dr["dateOfAssignment"];
                    r.dutyAssigned = (string)dr["dutyAssigned"];
                    r.hKeepId = (int)dr["HKeepId"];
                    r.hKeepName = (string)dr["hKeepName"];
                    result.Add(r);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }
    }
}

